This invention relates generally to database systems, and more particularly to an aggregate query in database systems.
A database is a collection of information. Relational databases are typically illustrated as one or more two-dimensional tables. Each table arranges the information in rows and columns. Each row corresponds to a record. Each column corresponds to a field. In a relational database a collection of tables can be related or joined to each other through a common field or key. The common key enables information in one table to be automatically cross-referenced to corresponding information in another table.
A complex search may be performed on a database with a query. A query specifies a set of criteria (e.g., the quantity of parts from a particular transaction) to define identified information for a database program to retrieve from the database. An aggregate query is a query that requests information concerning a selected group of records. For example, in a database which stores sales transactions, an aggregate query may request the total quantity of an item in a particular transaction. Each aggregate query may include a set of criteria to select records (e.g., grouping of records by an item code field and a transaction code field), and an operation to perform on the group of selected records (e.g., summing the quantity fields). Typical operations for aggregate queries include counting, summing, averaging, and finding minimum and maximum values.
To perform an aggregate query, a conventional database management system (DBMS) examines every record in the database to determine whether or not the record matches the set of criteria. The DBMS constructs a query table, known as a virtual table or view, from the records that match the set of criteria. The DBMS then performs the required aggregate operation over the appropriate fields from each record in the view.
As an illustration, the table below, TRANSACTION_TABLE, is one which can be queried using an aggregate query. The table has three fields arranged in a vertical format. The three fields are an ITEM_CODE field, a TRANSACTION_CODE field and a QUANTITY field. Separate records may have the same ITEM_CODE field and TRANSACTION_CODE field. An aggregate query may involve summing the QUANTITY fields of records sharing the same ITEM_CODE field and TRANSACTION_CODE field.
TABLE 1TRANSACTION_TABLEITEM_CODETRANSACTION_CODEQUANTITYItem_120200Item_12050Item_12170Item_122400Item_12250Item_12225Item_12450Item_12580Item_13065Item_220100Item_22185Item_22335Item_22380Item_226250Item_227320Item_22890
The output of the aggregate query may be tabulated in an OUTPUT_TABLE as follows:
TABLE 2OUTPUT_TABLESumSumSumSumSumSumSumSumSumSumSumITEM_CODE2021222324252627282930Item_12507047505080000065Item_2100850115002503209000
Such an aggregate query involves two steps and uses structured query language (SQL) statements as follows:
STEP 1: Create Intermediate Table or Viewcreate view trans_view (item_code, quantity20, quantity21,quantity22, quantity23, quantity24, quantity25, quantity26,quantity27, quantity28, quantity29, quantity 30)asselect item_code,sum(quantity) as quantity20,0,0,0,0,0,0,0,0,0,0,from transaction_table where transaction_code=20group by item_codeunionselect item_code,0sum(quantity) as quantity21,0,0,0,0,0,0,0,0,0,from transaction_table where transaction_code=21group by item_codeunionselect item_code,0,0,sum(quantity) as quantity22,0,0,0,0,0,0,0,0,from transaction_table where transaction_code=22group by item_codeunion...select item_code,0,0,0,0,0,0,0,0,0,0,sum(quantity) as quantity30,from transaction_table where transaction_code=30group by item_code;Step 2: Query the Intermediate Viewselect item_code,sum(quantity20) as sum20,sum(quantity21) as sum21,sum(quantity22) as sum22,sum(quantity23) as sum23,sum(quantity24) as sum24,sum(quantity25) as sum25,sum(quantity26) as sum26,sum(quantity27) as sum27,sum(quantity28) as sum28,sum(quantity29) as sum29,sum(quantity30) as sum30from trans_viewgroup by item_code;
As can be seen from the above statements, the first step involves generation of an intermediate table or view (trans_view) from information stored in the TRANSACTION_TABLE. The second step queries the intermediate view (trans_view) to produce the OUTPUT_TABLE. The query statements are lengthy and may be difficult to maintain.